from fastapi import APIRouter, HTTPException, UploadFile, File, Depends, Query, Body, BackgroundTasks
from fastapi.responses import StreamingResponse
from sqlalchemy.orm import Session
from sqlalchemy import func, desc, text
import pandas as pd
import io
from . import models, schemas
from backend.database.database import get_db
from backend.utils.db import get_db_connection
from pydantic import BaseModel, constr
from typing import List, Optional, Union, Annotated
import math
from backend.data_clean.enhanced_processor import EnhancedProcessor
from backend.data_clean.config_manager import config_manager
from backend.data_clean.models import BrandConfig, CommonProductName, MarketingWordConfig
import re
import logging
from backend.data_clean.api_service import call_deepseek_api
import json
import uuid
from datetime import datetime, timedelta
from backend.utils.logger import get_logger
from backend.utils.task_progress import save_task_progress, get_task_progress



# 导入数据清洗相关的组件
from backend.data_clean.text_cleaner import TextCleaner
from backend.data_clean.enhanced_specification_extractor import SpecificationExtractor
from backend.data_clean.enhanced_brand_recognition import JIEBA_AVAILABLE, BrandRecognizer
# 如果jieba可用，则导入jieba
if JIEBA_AVAILABLE:
    import jieba
# 设置日志记录器
logger = logging.getLogger(__name__)

router = APIRouter()

class IdsModel(BaseModel):
    ids: List[int]

class CleanModel(BaseModel):
    ids: Optional[List[int]] = None

class ImportResponse(BaseModel):
    success: bool
    total_records: int
    total_batches: int
    message: str

class CleanResponse(BaseModel):
    success: bool
    processed_count: int
    message: str

# 全局变量，用于存储清洗任务的状态
cleaning_tasks = {}

class CleaningTask:
    def __init__(self, total_count: int):
        self.task_id = str(uuid.uuid4())
        self.start_time = datetime.now()
        self.status = 'running'  # running, completed, error
        self.total_count = total_count
        self.processed_count = 0
        self.current_batch = 0
        self.total_batches = math.ceil(total_count / 100)  # 每批100条
        self.updated_count = 0
        self.error_count = 0
        self.message = '正在初始化...'
        self.last_update_time = datetime.now()
        self.timeout = 24 * 60 * 60  # 24小时超时（秒）
        self.progress = 0.0  # 进度百分比
        self.eta = None  # 预计完成时间
        self.speed = 0.0  # 处理速度（条/秒）
        self.time_elapsed = 0.0  # 已用时间（秒）
        self.time_remaining = None  # 预计剩余时间（秒）

    def update(self, processed_count: int = None):
        """
        更新任务状态和进度信息
        """
        current_time = datetime.now()
        self.last_update_time = current_time
        
        if processed_count is not None:
            self.processed_count = processed_count
            
        # 计算进度百分比
        self.progress = (self.processed_count / self.total_count) * 100 if self.total_count > 0 else 0
        
        # 计算已用时间
        self.time_elapsed = (current_time - self.start_time).total_seconds()
        
        # 计算处理速度
        if self.time_elapsed > 0:
            self.speed = self.processed_count / self.time_elapsed
            
            # 计算预计剩余时间
            if self.speed > 0:
                remaining_items = self.total_count - self.processed_count
                self.time_remaining = remaining_items / self.speed
                
                # 计算预计完成时间
                if self.time_remaining is not None:
                    self.eta = current_time + timedelta(seconds=self.time_remaining)

        # 更新进度消息
        self._update_progress_message()

    def _update_progress_message(self):
        """
        更新进度消息，包含进度条
        """
        # 生成进度条
        bar_width = 30
        filled = int(self.progress / 100 * bar_width)
        bar = '█' * filled + '░' * (bar_width - filled)
        
        # 格式化时间
        elapsed_str = self._format_time(self.time_elapsed)
        remaining_str = self._format_time(self.time_remaining) if self.time_remaining else "计算中..."
        eta_str = self.eta.strftime("%Y-%m-%d %H:%M:%S") if self.eta else "计算中..."
        
        # 构建消息
        self.message = (
            f"进度: [{bar}] {self.progress:.1f}%\n"
            f"批次: {self.current_batch}/{self.total_batches}\n"
            f"处理: {self.processed_count}/{self.total_count} 条\n"
            f"成功: {self.updated_count} 条, 失败: {self.error_count} 条\n"
            f"速度: {self.speed:.1f} 条/秒\n"
            f"已用时间: {elapsed_str}\n"
            f"预计剩余: {remaining_str}\n"
            f"预计完成: {eta_str}"
        )

    def _format_time(self, seconds: float) -> str:
        """
        格式化时间显示
        """
        if seconds is None:
            return "计算中..."
            
        hours = int(seconds // 3600)
        minutes = int((seconds % 3600) // 60)
        seconds = int(seconds % 60)
        
        if hours > 0:
            return f"{hours}小时{minutes}分{seconds}秒"
        elif minutes > 0:
            return f"{minutes}分{seconds}秒"
        else:
            return f"{seconds}秒"

    def is_timeout(self) -> bool:
        """
        检查任务是否超时，超时时间为24小时
        """
        # 如果任务已经完成，则不考虑超时
        if self.status == 'completed':
            return False
            
        # 如果任务正在运行且有进度，则根据当前速度预估是否能在24小时内完成
        if self.status == 'running' and self.speed > 0:
            remaining_items = self.total_count - self.processed_count
            estimated_remaining_time = remaining_items / self.speed
            return estimated_remaining_time > self.timeout
            
        # 如果无法预估，则使用传统的超时判断
        return (datetime.now() - self.start_time).total_seconds() > self.timeout

    def is_stalled(self) -> bool:
        """
        检查任务是否停滞（15分钟内没有更新）
        如果任务即将完成（进度>95%），则延长等待时间到30分钟
        """
        if self.progress > 95:
            stall_timeout = 1800  # 30分钟
        else:
            stall_timeout = 900  # 15分钟
        return (datetime.now() - self.last_update_time).total_seconds() > stall_timeout

# SKU列表（分页、搜索）
@router.get('/list')
def get_sku_list(
    page: int = 1,
    page_size: int = 20,
    search: str = '',
    classification: str = '',
    is_handled: Optional[bool] = None,
    db: Session = Depends(get_db)
):
    """
    获取SKU列表，支持分页、搜索、分类筛选和处理状态筛选
    """
    try:
        query = db.query(models.ItemSKU)
        
        # 添加搜索条件
        if search:
            query = query.filter(models.ItemSKU.item_name.ilike(f'%{search}%'))
            
        # 添加分类筛选条件
        if classification:
            query = query.filter(models.ItemSKU.item_classification == classification)
            
        # 添加处理状态筛选条件
        if is_handled is not None:
            if is_handled:
                # 筛选已处理的记录
                query = query.filter(models.ItemSKU.is_handled == True)
            else:
                # 筛选未处理的记录
                query = query.filter(
                    (models.ItemSKU.is_handled == False) | 
                    (models.ItemSKU.is_handled.is_(None))
                )
            
        # 计算总数
        total = query.count()
        
        # 分页
        items = query.offset((page - 1) * page_size).limit(page_size).all()
        
        return {
            "items": items,
            "total": total,
            "page": page,
            "page_size": page_size
        }
    except Exception as e:
        logger.error(f"获取SKU列表失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"获取SKU列表失败: {str(e)}")

# 新增SKU
@router.post('/add')
def add_sku(sku: schemas.ItemSKUCreate, db: Session = Depends(get_db)):
    db_sku = models.ItemSKU(**sku.dict())
    db.add(db_sku)
    db.commit()
    db.refresh(db_sku)
    return schemas.ItemSKU.from_orm(db_sku)

# 编辑SKU
@router.post('/update')
def update_sku(sku: schemas.ItemSKUUpdate, db: Session = Depends(get_db)):
    db_sku = db.query(models.ItemSKU).filter(models.ItemSKU.id == sku.id).first()
    if not db_sku:
        raise HTTPException(status_code=404, detail="SKU不存在")
    for k, v in sku.dict(exclude_unset=True).items():
        setattr(db_sku, k, v)
    db.commit()
    db.refresh(db_sku)
    return schemas.ItemSKU.from_orm(db_sku)

# 删除SKU（支持批量）
@router.post('/delete')
def delete_sku(ids_model: IdsModel, db: Session = Depends(get_db)):
    ids = ids_model.ids
    # logger(f"Deleting SKUs with IDs: {ids}")
    db.query(models.ItemSKU).filter(models.ItemSKU.id.in_(ids)).delete(synchronize_session=False)
    db.commit()
    return {"success": True}

# 批量清洗SKU数据，使用data_clean模块的功能
@router.post('/clean')
def clean_sku(request: dict = Body(default={}), db: Session = Depends(get_db)):
    """
    批量清洗SKU数据，使用data_clean模块的功能
    流程：先提取规格信息，再提取品牌信息，最后生成清洗后名称
    """

    logger.info(f"清洗SKU数据请求: {request}")

    try:
        # 从请求中提取ID列表
        ids = request.get('ids', [])
        
        logger.info(f"提取的ID列表: {ids}")

        # 初始化清洗所需的组件
        try:
            # 1. 初始化配置管理器 - 加载品牌、单位等信息
            config_manager.initialize(db)
            
            # 2. 初始化各个处理组件
            text_cleaner = TextCleaner(
                marketing_words=config_manager.marketing_words,
                product_names=config_manager.common_products
            )
            
            spec_extractor = SpecificationExtractor(
                units_list=config_manager.units,
                unit_conversion=config_manager.unit_conversion,
                standard_unit_map=config_manager.standard_unit_map
            )
            
            brand_recognizer = BrandRecognizer(
                brands=config_manager.brands,
                min_similarity=85
            )
            
            # 添加自定义词典
            if JIEBA_AVAILABLE:
                # 添加品牌到jieba词典
                for brand in config_manager.brands:
                    if brand and len(brand.strip()) > 1:
                        jieba.add_word(brand.strip(), freq=1000, tag='nz')
                
                # 添加商品名到jieba词典
                for product in config_manager.common_products:
                    if product and len(product.strip()) > 1:
                        jieba.add_word(product.strip(), freq=1000, tag='n')
                
                # 添加单位到jieba词典
                for unit in config_manager.units:
                    if unit and len(unit.strip()) >= 1:
                        jieba.add_word(unit.strip(), freq=1000, tag='n')
                
                logger.info("已添加自定义词典到jieba")
            
            logger.info("清洗组件初始化完成")
            
        except Exception as e:
            logger.error(f"初始化清洗组件失败: {str(e)}")
            # 使用最简单的组件
            text_cleaner = None
            spec_extractor = None
            brand_recognizer = None
        
        # 确定需要处理的记录
        if ids:
            # 处理指定的SKU
            items = db.query(models.ItemSKU).filter(models.ItemSKU.id.in_(ids)).all()
        else:
            # 处理所有SKU - 使用批处理方式
            batch_size = 100  # 每批处理的记录数
            total_count = db.query(func.count(models.ItemSKU.id)).scalar()
            
            if total_count == 0:
                return {"success": False, "processed_count": 0, "message": "未找到需要处理的SKU记录"}
            
            # 处理统计
            total_processed = 0
            total_updated = 0
            
            # 分批处理所有记录
            for offset in range(0, total_count, batch_size):
                batch = db.query(models.ItemSKU).offset(offset).limit(batch_size).all()
                
                processed_count = 0
                updated_count = 0
                
                for item in batch:
                    try:
                        if not item.item_name:
                            continue
                        
                        # 处理单个SKU
                        result = process_single_sku(item, text_cleaner, spec_extractor, brand_recognizer)
                        
                        # 如果有更新，则计数
                        if result:
                            updated_count += 1
                        
                        processed_count += 1
                    
                    except Exception as e:
                        logger.error(f"处理SKU ID {item.id} 失败: {str(e)}")
                        continue  # 忽略单个SKU的处理错误
                
                # 提交每批更改
                db.commit()
                
                # 累加总数
                total_processed += processed_count
                total_updated += updated_count
                
                logger.info(f"已处理 {total_processed}/{total_count} 条记录, 更新 {total_updated} 条")
            
            # 返回总结果
            return {
                "success": True,
                "processed_count": total_processed,
                "message": f"成功处理 {total_processed} 条SKU记录，更新 {total_updated} 条"
            }
            
        # 如果指定了ID，则处理指定的SKU
        if not items:
            return {"success": False, "processed_count": 0, "message": "未找到需要处理的SKU记录"}
        
        # 处理统计
        processed_count = 0
        updated_count = 0
        
        for item in items:
            try:
                if not item.item_name:
                    continue
                
                # 处理单个SKU
                result = process_single_sku(item, text_cleaner, spec_extractor, brand_recognizer)
                
                # 如果有更新，则计数
                if result:
                    updated_count += 1
                
                processed_count += 1
            
            except Exception as e:
                logger.error(f"处理SKU ID {item.id} 失败: {str(e)}")
                continue  # 忽略单个SKU的处理错误
        
        # 提交更改
        db.commit()
        
        # 返回结果
        return {
            "success": True,
            "processed_count": processed_count,
            "message": f"成功处理 {processed_count} 条SKU记录，更新 {updated_count} 条"
        }
    
    except Exception as e:
        logger.error(f"SKU数据清洗失败: {str(e)}")
        db.rollback()
        return {"success": False, "message": f"SKU数据清洗失败: {str(e)}"}

def process_single_sku(item, text_cleaner, spec_extractor, brand_recognizer):
    """
    处理单个SKU的清洗逻辑，主要使用 DeepSeek API 进行处理
    """
    if not item.item_name:
        return False
    
    logger.info(f"开始清洗SKU: {item.id}, 名称: {item.item_name}")
    
    # 初始化返回值
    has_updates = False
    
    # 原始文本
    original_text = item.item_name
    
    # 1. AI 标注和分词
    try:
        ai_result = ai_label_sku(original_text, config_manager)
        logger.info(f"AI 标注结果: {ai_result}")
        
        if not ai_result:
            logger.warning("AI 标注失败，使用备用规则处理")
            return process_single_sku_fallback(item, text_cleaner, spec_extractor, brand_recognizer)
        
        # 更新SKU对象
        if ai_result.get('brand'):
            item.item_brand = ai_result['brand']
            has_updates = True
        
        if ai_result.get('specs'):
            item.item_specs = ai_result['specs']
            has_updates = True
        
        if ai_result.get('cleaned_name'):
            item.cleaned_name = ai_result['cleaned_name']
            has_updates = True
        
        if ai_result.get('base_unit'):
            item.base_unit = ai_result['base_unit']
            has_updates = True
        
        # 记录分词和营销词信息到日志
        if ai_result.get('tokens'):
            logger.info(f"分词结果: {', '.join(ai_result['tokens'])}")
        
        if ai_result.get('marketing_words'):
            logger.info(f"识别出的营销词: {', '.join(ai_result['marketing_words'])}")
        
        return has_updates
        
    except Exception as e:
        logger.error(f"AI 处理失败: {str(e)}")
        return process_single_sku_fallback(item, text_cleaner, spec_extractor, brand_recognizer)

def process_single_sku_fallback(item, text_cleaner, spec_extractor, brand_recognizer):
    """
    当 AI 处理失败时的备用处理逻辑
    """
    try:
        has_updates = False
        processed_text = item.item_name
        
        # 基本文本清理
        processed_text = re.sub(r'\(.*?\)', '', processed_text)
        processed_text = re.sub(r'\{.*?\}', '', processed_text)
        processed_text = re.sub(r'\[.*?\]', '', processed_text)
        processed_text = re.sub(r'【.*?】', '', processed_text)
        processed_text = re.sub(r'[*+~!@#$^&()_={}[\]|\\:;<>?]', ' ', processed_text)
        processed_text = re.sub(r'\s+', ' ', processed_text).strip()
        
        # 使用规则提取器
        if spec_extractor:
            try:
                spec_result = spec_extractor.extract_specifications(processed_text)
                if spec_result['spec_text'] and spec_result['spec_value'] > 0:
                    item.item_specs = spec_result['spec_text']
                    has_updates = True
                    if spec_result['standard_value'] > 0 and spec_result['standard_unit']:
                        item.base_unit = spec_result['standard_unit']
                        has_updates = True
                    processed_text = spec_result['cleaned_text']
            except Exception as e:
                logger.error(f"规格提取失败: {str(e)}")
        
        if brand_recognizer:
            try:
                brand_result = brand_recognizer.extract_and_clean_brands(processed_text)
                if brand_result.get('brand_string'):
                    item.item_brand = brand_result['brand_string']
                    has_updates = True
                    processed_text = brand_result['cleaned_text']
            except Exception as e:
                logger.error(f"品牌识别失败: {str(e)}")
        
        # 最终清理
        processed_text = re.sub(r'[\(（].*?[\)）]', '', processed_text)
        processed_text = re.sub(r'\s+', ' ', processed_text).strip()
        processed_text = re.sub(r'[,，。.、/\\"\']+', '', processed_text).strip()
        
        if processed_text != item.item_name:
            item.cleaned_name = processed_text
            has_updates = True
        
        return has_updates
        
    except Exception as e:
        logger.error(f"备用处理失败: {str(e)}")
        return False

# 导入SKU（Excel/CSV）
@router.post('/import')
def import_sku(file: UploadFile = File(...), db: Session = Depends(get_db)):
    ext = file.filename.split('.')[-1].lower()
    if ext == 'csv':
        df = pd.read_csv(file.file)
    else:
        df = pd.read_excel(file.file)
    for _, row in df.iterrows():
        db_sku = models.ItemSKU(**row.to_dict())
        db.add(db_sku)
    db.commit()
    return {"success": True, "count": len(df)}

# 导出SKU（Excel/CSV）
@router.get('/export')
def export_sku(format: str = 'xlsx', db: Session = Depends(get_db)):
    skus = db.query(models.ItemSKU).all()
    df = pd.DataFrame([s.__dict__ for s in skus])
    buf = io.BytesIO()
    if format == 'csv':
        df.to_csv(buf, index=False)
        buf.seek(0)
        return StreamingResponse(buf, media_type='text/csv', headers={'Content-Disposition': 'attachment; filename=sku.csv'})
    else:
        df.to_excel(buf, index=False)
        buf.seek(0)
        return StreamingResponse(buf, media_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', headers={'Content-Disposition': 'attachment; filename=sku.xlsx'})

# 从商品表批量导入数据到SKU表
@router.get('/import-from-items')
def import_from_items(
    batch_size: int = Query(100, description="每批处理的记录数"),
    shop_id: Optional[int] = Query(None, description="按超市ID筛选"),
    db: Session = Depends(get_db)
):
    try:
        conn = get_db_connection()
        
        # 构建查询条件
        where_clause = ""
        params = []
        
        if shop_id:
            where_clause = "WHERE shop_id = ?"
            params.append(shop_id)
        
        # 1. 计算总记录数和批次数
        count_query = f"""
            SELECT COUNT(DISTINCT sku_id) as total
            FROM item
            {where_clause}
        """
        total_records = conn.execute(count_query, params).fetchone()[0]
        total_batches = math.ceil(total_records / batch_size)
        
        # 2. 获取所有不重复的sku_id列表，按shop_id和id排序
        # 使用窗口函数获取每个sku_id的最新记录
        sku_query = f"""
            WITH RankedItems AS (
                SELECT 
                    i.*,
                    ROW_NUMBER() OVER (PARTITION BY sku_id ORDER BY created_at DESC) as rn
                FROM item i
                {where_clause}
            )
            SELECT 
                id, item_name, item_brand, item_specs, item_price, 
                item_classification, base_price, base_unit, sku_id
            FROM RankedItems
            WHERE rn = 1
            ORDER BY shop_id, id
        """
        
        all_items = conn.execute(sku_query, params).fetchall()
        conn.close()
        
        # 3. 分批处理并插入数据
        processed_count = 0
        
        for i in range(0, len(all_items), batch_size):
            batch_items = all_items[i:i+batch_size]
            
            for item in batch_items:
                # 检查SKU是否已存在
                existing_sku = db.query(models.ItemSKU).filter(models.ItemSKU.sku == item['sku_id']).first()
                
                if existing_sku:
                    # 更新现有记录
                    existing_sku.item_name = item['item_name']
                    existing_sku.item_brand = item['item_brand']
                    existing_sku.item_specs = item['item_specs']
                    existing_sku.item_price = item['item_price']
                    existing_sku.item_classification = item['item_classification']
                    existing_sku.base_price = item['base_price']
                    existing_sku.base_unit = item['base_unit']
                else:
                    # 创建新记录
                    new_sku = models.ItemSKU(
                        item_name=item['item_name'],
                        item_brand=item['item_brand'],
                        item_specs=item['item_specs'],
                        item_price=item['item_price'],
                        item_classification=item['item_classification'],
                        base_price=item['base_price'],
                        base_unit=item['base_unit'],
                        sku=item['sku_id']
                    )
                    db.add(new_sku)
            
            # 提交每批数据
            db.commit()
            processed_count += len(batch_items)
        
        return ImportResponse(
            success=True,
            total_records=total_records,
            total_batches=total_batches,
            message=f"成功导入 {processed_count} 条记录，共 {total_batches} 批次"
        )
    
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"导入失败: {str(e)}")

# SKU详情
@router.get('/{id}')
def sku_detail(id: int, db: Session = Depends(get_db)):
    sku = db.query(models.ItemSKU).filter(models.ItemSKU.id == id).first()
    if not sku:
        raise HTTPException(status_code=404, detail="SKU不存在")
    return schemas.ItemSKU.from_orm(sku)

# 新增：自动标注相关路由
@router.post('/auto-label')
def auto_label_sku(request: schemas.AutoLabelRequest, db: Session = Depends(get_db)):
    """
    自动标注SKU信息
    支持单条和批量模式
    使用 DeepSeek API 进行智能标注
    """
    try:
        # 1. 初始化配置管理器和清洗组件
        try:
            config_manager.initialize(db)
            logger.info("配置管理器初始化完成")
            
            text_cleaner = TextCleaner(
                marketing_words=config_manager.marketing_words,
                product_names=config_manager.common_products
            )
            logger.info("文本清洗器初始化完成")
            
            spec_extractor = SpecificationExtractor(
                units_list=config_manager.units,
                unit_conversion=config_manager.unit_conversion,
                standard_unit_map=config_manager.standard_unit_map
            )
            logger.info("规格提取器初始化完成")
            
            brand_recognizer = BrandRecognizer(
                brands=config_manager.brands,
                min_similarity=85
            )
            logger.info("品牌识别器初始化完成")
            
        except Exception as e:
            logger.error(f"初始化清洗组件失败: {str(e)}")
            raise HTTPException(status_code=500, detail=f"初始化清洗组件失败: {str(e)}")

        # 2. 处理请求
        if request.batch_mode and request.item_ids:
            # 批量处理模式
            if not request.item_ids:
                raise HTTPException(status_code=400, detail="批量处理模式需要提供item_ids")
            
            logger.info(f"开始批量处理，共{len(request.item_ids)}条记录")
            
            # 获取所有SKU记录
            items = db.query(models.ItemSKU).filter(models.ItemSKU.id.in_(request.item_ids)).all()
            if not items:
                raise HTTPException(status_code=404, detail="未找到指定的SKU记录")
            
            results = []
            success_count = 0
            error_count = 0
            
            # 批量处理每个SKU
            for item in items:
                try:
                    logger.info(f"处理SKU ID: {item.id}, 名称: {item.item_name}")
                    
                    if not item.item_name:
                        logger.warning(f"SKU ID {item.id} 名称为空，跳过处理")
                        results.append(schemas.AutoLabelResponse(
                            message="商品名称为空，无法处理"
                        ))
                        continue
                    
                    # 调用AI处理
                    ai_result = ai_label_sku(item.item_name, config_manager)
                    logger.info(f"AI标注结果: {ai_result}")
                    
                    if ai_result:
                        for result in ai_result:
                            # 更新SKU信息
                            if result.get('brand'):
                                item.item_brand = result['brand']
                            if ai_result.get('specs'):
                                item.item_specs = ai_result['specs']
                            if ai_result.get('cleaned_name'):
                                item.cleaned_name = ai_result['cleaned_name']
                            if result.get('base_unit'):
                                item.base_unit = result['base_unit']
                            
                            # 提交更改
                            try:
                                db.commit()
                                success_count += 1
                                results.append(schemas.AutoLabelResponse(
                                    item_brand=item.item_brand,
                                    item_specs=item.item_specs,
                                    cleaned_name=item.cleaned_name,
                                    base_unit=item.base_unit,
                                    confidence=ai_result.get('confidence', 0.9),
                                    message="自动标注成功"
                                ))
                                logger.info(f"SKU ID {item.id} 处理成功")
                            except Exception as e:
                                db.rollback()
                                error_count += 1
                                error_msg = f"数据库更新失败: {str(e)}"
                                logger.error(f"SKU ID {item.id} {error_msg}")
                                results.append(schemas.AutoLabelResponse(
                                    message=error_msg
                                ))
                    else:
                        # AI处理失败，尝试规则处理
                        logger.warning(f"SKU ID {item.id} AI处理失败，尝试规则处理")
                        result = process_single_sku_fallback(item, text_cleaner, spec_extractor, brand_recognizer)
                        
                        if result:
                            try:
                                db.commit()
                                success_count += 1
                                results.append(schemas.AutoLabelResponse(
                                    item_brand=item.item_brand,
                                    item_specs=item.item_specs,
                                    cleaned_name=item.cleaned_name,
                                    base_unit=item.base_unit,
                                    confidence=0.7,  # 规则处理的默认置信度
                                    message="规则处理成功"
                                ))
                                logger.info(f"SKU ID {item.id} 规则处理成功")
                            except Exception as e:
                                db.rollback()
                                error_count += 1
                                error_msg = f"数据库更新失败: {str(e)}"
                                logger.error(f"SKU ID {item.id} {error_msg}")
                                results.append(schemas.AutoLabelResponse(
                                    message=error_msg
                                ))
                        else:
                            error_count += 1
                            results.append(schemas.AutoLabelResponse(
                                message="无法识别有效信息"
                            ))
                            logger.warning(f"SKU ID {item.id} 无法识别有效信息")
                    
                except Exception as e:
                    error_count += 1
                    error_msg = f"处理失败: {str(e)}"
                    logger.error(f"SKU ID {item.id} {error_msg}")
                    results.append(schemas.AutoLabelResponse(
                        message=error_msg
                    ))
            
            # 返回批量处理结果
            return schemas.BatchAutoLabelResponse(
                results=results,
                total=len(items),
                success_count=success_count,
                error_count=error_count,
                message=f"批量处理完成，成功{success_count}条，失败{error_count}条"
            )
        
        else:
            # 单条处理模式
            if not request.item_name:
                raise HTTPException(status_code=400, detail="单条处理模式需要提供item_name")
            
            logger.info(f"开始处理单条记录: {request.item_name}")
            
            # 创建临时SKU对象
            temp_sku = models.ItemSKU(
                item_name=request.item_name,
                sku="TEMP"
            )
            
            # 调用AI处理
            ai_result = ai_label_sku(request.item_name, config_manager)
            logger.info(f"AI标注结果: {ai_result}")
            
            if ai_result:
                return schemas.AutoLabelResponse(
                    item_brand=ai_result.get('brand'),
                    item_specs=ai_result.get('specs'),
                    cleaned_name=ai_result.get('cleaned_name'),
                    base_unit=ai_result.get('base_unit'),
                    confidence=ai_result.get('confidence', 0.9),
                    message="自动标注成功"
                )
            else:
                # AI处理失败，尝试规则处理
                logger.warning("AI处理失败，尝试规则处理")
                result = process_single_sku_fallback(temp_sku, text_cleaner, spec_extractor, brand_recognizer)
                
                if result:
                    return schemas.AutoLabelResponse(
                        item_brand=temp_sku.item_brand,
                        item_specs=temp_sku.item_specs,
                        cleaned_name=temp_sku.cleaned_name,
                        base_unit=temp_sku.base_unit,
                        confidence=0.7,
                        message="规则处理成功"
                    )
                else:
                    return schemas.AutoLabelResponse(
                        message="无法识别有效信息"
                    )
    
    except Exception as e:
        logger.error(f"自动标注失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"自动标注失败: {str(e)}")

def ai_label_sku(item_names: List[str], config_manager) -> List[dict]:
    """
    使用 DeepSeek API 进行智能标注和分词
    
    Args:
        item_names: 单个商品名称或商品名称列表
        config_manager: 配置管理器
        
    Returns:
        始终返回List[dict]，即使是单个商品也返回长度为1的列表
    """
    try:
        # 统一转换为列表处理
        if isinstance(item_names, str):
            item_names = [item_names]
        
        # 对空值和无效值进行过滤
        valid_names = [name.strip() for name in item_names if name and isinstance(name, str) and name.strip()]
        if not valid_names:
            logger.warning("没有有效的商品名称需要处理")
            return []
            
        # 构建批量处理的提示词
        items_text = "\n".join([f"商品{i+1}：{name}" for i, name in enumerate(valid_names)])
        prompt = f"""请分析以下商品名称列表，提取每个商品的关键信息并进行分词。分析前，请先将促销、热门等类似的无用字段，以及由【】或[]两种括号及里面的文字信息先去除。对于每个商品，需要识别：
1. 包装单位：判断是盒、包、袋、瓶还是散装。如果没有明确的包装单位，则视为散装。
2. 品牌名称：提取商品的品牌。
3. 规格信息：包括数值和单位。
4. 基础单位：当前商品的最小拆分单位。
5. 清洗后的名称：去除营销词、规格等信息后的核心商品名。
6. 数量：当前商品根基最小单位可以拆分出几个。

商品列表：
{items_text}

请以JSON数组格式返回分析结果，每个商品对应一个对象：
[
    {{
        "package_type": "盒/包/袋/瓶/散装",
        "brand": "品牌名称",
        "specs": "规格信息",
        "base_unit": "基础单位",
        "cleaned_name": "清洗后名称",
        "base_num": "数量",
        "confidence": 0.95
    }}
]

只返回JSON格式数据，不要其他说明文字。"""

        # 添加重试机制
        max_retries = 3
        retry_count = 0
        
        while retry_count < max_retries:
            try:
                # 调用 DeepSeek API
                response = call_deepseek_api(prompt, config_manager)
                
                if response and response.get('choices') and len(response['choices']) > 0:
                    content = response['choices'][0]['message']['content']
                    # 解析返回的 JSON
                    try:
                        results = json.loads(content)
                        # 确保返回的是列表类型
                        if not isinstance(results, list):
                            logger.error(f"AI 返回结果格式错误，期望列表但得到: {type(results)}")
                            retry_count += 1
                            continue
                            
                        # 验证结果数量是否匹配
                        if len(results) != len(valid_names):
                            logger.warning(f"AI返回结果数量({len(results)})与输入数量({len(valid_names)})不匹配")
                            # 如果结果数量少于输入，补充空结果
                            while len(results) < len(valid_names):
                                results.append({
                                    "package_type": "散装",
                                    "brand": "",
                                    "specs": "",
                                    "base_unit": "",
                                    "cleaned_name": valid_names[len(results)],
                                    "confidence": 0.5
                                })
                        
                        # 验证每个结果的格式
                        for i, result in enumerate(results):
                            if not isinstance(result, dict):
                                results[i] = {
                                    "package_type": "散装",
                                    "brand": "",
                                    "specs": "",
                                    "base_unit": "",
                                    "cleaned_name": valid_names[i] if i < len(valid_names) else "",
                                    "confidence": 0.5
                                }
                        
                        return results
                        
                    except json.JSONDecodeError as e:
                        logger.error(f"AI 返回结果解析失败: {content}, 错误: {str(e)}")
                        retry_count += 1
                        continue
                
                retry_count += 1
                
            except Exception as e:
                logger.error(f"调用 AI API 失败: {str(e)}")
                retry_count += 1
                continue
        
        # 如果所有重试都失败，返回默认结果
        logger.error("AI处理失败，返回默认结果")
        return [
            {
                "package_type": "散装",
                "brand": "",
                "specs": "",
                "base_unit": "",
                "cleaned_name": name,
                "confidence": 0.5
            }
            for name in valid_names
        ]
    
    except Exception as e:
        logger.error(f"AI 标注失败: {str(e)}")
        return None


def merge_label_results(rule_based_result: dict, ai_result: dict) -> dict:
    """
    合并规则标注和 AI 标注的结果
    优先使用置信度高的结果
    """
    if not ai_result:
        return rule_based_result
    
    merged = {}
    
    # AI 结果的置信度
    ai_confidence = ai_result.get('confidence', 0)
    # 规则标注的默认置信度设为 0.7
    rule_confidence = 0.7
    
    # 品牌
    if ai_confidence > 0.8 and ai_result.get('brand'):
        merged['item_brand'] = ai_result['brand']
    else:
        merged['item_brand'] = rule_based_result.get('item_brand')
    
    # 规格
    if ai_confidence > 0.8 and ai_result.get('specs'):
        merged['item_specs'] = ai_result['specs']
    else:
        merged['item_specs'] = rule_based_result.get('item_specs')
    
    # 清洗后名称
    if ai_confidence > 0.8 and ai_result.get('cleaned_name'):
        merged['cleaned_name'] = ai_result['cleaned_name']
    else:
        merged['cleaned_name'] = rule_based_result.get('cleaned_name')

    # 数量
    if ai_confidence > 0.8 and ai_result.get('base_num'):
        merged['base_num'] = ai_result['base_num']
    else:
        merged['base_num'] = rule_based_result.get('base_num')
    
    # 标准单位
    if ai_confidence > 0.8 and ai_result.get('base_unit'):
        merged['base_unit'] = ai_result['base_unit']
    else:
        merged['base_unit'] = rule_based_result.get('base_unit')
    
    # 使用较高的置信度
    merged['confidence'] = max(ai_confidence, rule_confidence)
    
    return merged 

@router.post('/ai-clean')
def ai_clean_sku(
    background_tasks: BackgroundTasks,
    db: Session = Depends(get_db),
    request: dict = Body(default={})
):
    """
    启动AI清洗任务
    返回任务ID用于前端轮询进度
    支持重复执行，只处理未标记的记录(is_handled != 1)
    """
    try:
        # 从请求中提取ID列表和选项
        ids = request.get('ids', [])
        skip_handled = request.get('skip_handled', True)  # 默认跳过已处理的记录
        
        # 初始化配置管理器
        config_manager.initialize(db)
        
        # 确定需要处理的记录
        if ids:
            # 处理指定的SKU
            query = db.query(models.ItemSKU).filter(models.ItemSKU.id.in_(ids))
            # 如果启用跳过已处理，则添加过滤条件
            if skip_handled:
                query = query.filter(
                    (models.ItemSKU.is_handled.is_(False)) | 
                    (models.ItemSKU.is_handled.is_(None))
                )
            items = query.all()
        else:
            # 处理所有SKU
            query = db.query(models.ItemSKU)
            # 如果启用跳过已处理，则添加过滤条件
            if skip_handled:
                query = query.filter(
                    (models.ItemSKU.is_handled.is_(False)) | 
                    (models.ItemSKU.is_handled.is_(None))
                )
            items = query.all()
        
        total_count = len(items)
        if total_count == 0:
            if skip_handled:
                return {
                    "success": False,
                    "message": "未找到需要处理的SKU记录（已跳过已标记记录）"
                }
            else:
                return {
                    "success": False,
                    "message": "未找到需要处理的SKU记录"
                }
        
        # 创建新的清洗任务
        task = CleaningTask(total_count)
        cleaning_tasks[task.task_id] = task
        
        # 启动异步处理
        background_tasks.add_task(process_ai_clean, task.task_id, items, db)
        logger.info(f"AI清洗任务已启动: {task.task_id}, 总记录数: {total_count}, 跳过已处理: {skip_handled}")
        return {
            "success": True,
            "task_id": task.task_id,
            "message": f"AI清洗任务已启动，将处理 {total_count} 条记录"
        }
        
    except Exception as e:
        logger.error(f"启动AI清洗任务失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"启动AI清洗任务失败: {str(e)}")

@router.get('/task-progress/{task_id}')
async def get_task_status(task_id: str):
    """获取任务进度"""
    try:
        progress = get_task_progress(task_id)
        return progress
    except Exception as e:
        logger.error(f"获取任务进度失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"获取任务进度失败: {str(e)}")

async def process_ai_clean(task_id: str, items: List[models.ItemSKU], db: Session):
    """
    异步处理AI清洗任务
    """
    task = cleaning_tasks[task_id]
    batch_size = 100
    
    # 创建新的数据库会话
    from backend.database.database import SessionLocal
    db = SessionLocal()
    
    try:
        # 重新从数据库加载所有项目
        item_ids = [item.id for item in items]
        items = db.query(models.ItemSKU).filter(models.ItemSKU.id.in_(item_ids)).all()
        
        # 更新初始状态
        task.message = f"开始处理，共 {task.total_count} 条记录"
        task.update(0)  # 初始化进度
        
        # 按批次处理记录
        for batch_index in range(task.total_batches):
            if task.is_timeout():
                task.status = 'error'
                task.message = '任务超时'
                db.close()
                return
                
            start_idx = batch_index * batch_size
            end_idx = min(start_idx + batch_size, task.total_count)
            current_batch = items[start_idx:end_idx]
            
            # 更新批次开始状态
            task.current_batch = batch_index + 1
            task.update(task.processed_count)  # 更新进度
            
            try:
                # 收集这一批次的商品名称和对应的SKU ID
                batch_data = [(item.id, item.item_name) for item in current_batch if item.item_name]
                if not batch_data:
                    continue
                    
                batch_ids, batch_names = zip(*batch_data)
                
                # 批量调用AI接口进行分析
                if task.is_timeout():
                    task.status = 'error'
                    task.message = '任务超时'
                    db.close()
                    return

                try:
                    # 批量调用AI接口
                    results = ai_label_sku(batch_names, config_manager)
                    
                    if results and isinstance(results, list):
                        # 准备批量更新数据
                        update_data = []
                        for item_id, result, original_name in zip(batch_ids, results, batch_names):
                            if isinstance(result, dict):
                                update_data.append({
                                    'id': item_id,
                                    'item_name': original_name,  # 添加原始item_name
                                    'package_type': result.get('package_type', '散装'),
                                    'item_brand': result.get('brand', ''),
                                    'item_specs': result.get('specs', ''),
                                    'cleaned_name': result.get('cleaned_name', ''),
                                    'base_unit': result.get('base_unit', '')
                                })
                        
                        if update_data:
                            # 使用批量更新，并标记为已处理
                            update_stmt = text("""
                                UPDATE item_sku 
                                SET 
                                    item_name = :item_name,  # 添加item_name字段
                                    package_type = :package_type,
                                    item_brand = :item_brand,
                                    item_specs = :item_specs,
                                    cleaned_name = :cleaned_name,
                                    base_unit = :base_unit,
                                    is_handled = TRUE,
                                    updated_at = CURRENT_TIMESTAMP
                                WHERE id = :id
                            """)
                            
                            # 执行批量更新
                            for data in update_data:
                                try:
                                    db.execute(update_stmt, data)
                                    task.updated_count += 1
                                except Exception as e:
                                    logger.error(f"更新SKU ID {data['id']} 失败: {str(e)}")
                                    task.error_count += 1
                                finally:
                                    task.processed_count += 1
                                    # 实时更新进度
                                    if task.processed_count % 10 == 0:  # 每处理10条更新一次进度
                                        task.update(task.processed_count)
                            
                            # 提交事务
                            try:
                                db.commit()
                            except Exception as e:
                                logger.error(f"提交批次 {task.current_batch} 失败: {str(e)}")
                                db.rollback()
                                task.error_count += len(update_data)
                                continue
                    else:
                        logger.error(f"AI返回结果格式不正确: {results}")
                        task.error_count += len(batch_names)
                        task.processed_count += len(batch_names)
                        task.update(task.processed_count)
                        
                except Exception as e:
                    logger.error(f"处理批次 {task.current_batch} 失败: {str(e)}")
                    task.error_count += len(batch_names)
                    task.processed_count += len(batch_names)
                    task.update(task.processed_count)
                    continue
                
                # 更新任务状态
                task.update(task.processed_count)
                
            except Exception as e:
                logger.error(f"处理批次 {batch_index + 1} 失败: {str(e)}")
                task.error_count += len(current_batch)
                task.processed_count += len(current_batch)
                task.update(task.processed_count)
                continue
        
        # 更新最终状态
        task.status = 'completed'
        task.update(task.processed_count)
        
        logger.info(f"处理完成：共处理 {task.processed_count} 条记录，" \
                   f"更新 {task.updated_count} 条，失败 {task.error_count} 条")
        
    except Exception as e:
        logger.error(f"AI清洗过程发生错误: {str(e)}")
        task.status = 'error'
        task.message = f"AI清洗失败: {str(e)}"
        task.update(task.processed_count)
        db.rollback()
    finally:
        # 确保关闭数据库会话
        db.close()

@router.get('/classifications')
def get_classifications(db: Session = Depends(get_db)):
    """
    获取所有SKU的分类列表
    """
    try:
        # 查询所有不重复的分类
        classifications = db.query(models.ItemSKU.item_classification)\
            .filter(models.ItemSKU.item_classification.isnot(None))\
            .distinct()\
            .all()
        
        # 将查询结果转换为列表
        classification_list = [c[0] for c in classifications if c[0]]
        classification_list.sort()  # 按字母顺序排序
        
        return classification_list
    except Exception as e:
        logger.error(f"获取分类列表失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"获取分类列表失败: {str(e)}")

@router.post('/update-base-num')
async def update_base_num(db: Session = Depends(get_db)):
    """更新SKU的基础数量"""
    try:
        # 创建任务ID
        task_id = str(uuid.uuid4())
        
        # 获取所有需要处理的SKU
        skus = db.query(models.ItemSKU).all()
        total_count = len(skus)
        processed_count = 0
        updated_count = 0
        error_count = 0
        
        for sku in skus:
            try:
                if not sku.item_specs:
                    continue
                
                # 使用新的提取逻辑，传入 base_unit
                base_num = extract_numbers_from_specs(sku.item_specs, sku.base_unit)
                
                if base_num is not None:
                    sku.base_num = base_num
                    updated_count += 1
                
                processed_count += 1
                
                # 每处理100条记录保存一次
                if processed_count % 100 == 0:
                    db.commit()
                    
                    # 更新进度
                    save_task_progress(task_id, {
                        "status": "processing",
                        "total_count": total_count,
                        "processed_count": processed_count,
                        "updated_count": updated_count,
                        "error_count": error_count,
                        "message": f"已处理 {processed_count}/{total_count} 条记录"
                    })
                    
            except Exception as e:
                logger.error(f"处理SKU {sku.id} 时出错: {str(e)}")
                error_count += 1
        
        # 最后一次提交
        db.commit()
        
        # 更新最终进度
        save_task_progress(task_id, {
            "status": "completed",
            "total_count": total_count,
            "processed_count": processed_count,
            "updated_count": updated_count,
            "error_count": error_count,
            "message": f"处理完成，共更新 {updated_count} 条记录，失败 {error_count} 条"
        })
        
        return {
            "success": True,
            "task_id": task_id,
            "message": "更新任务已启动"
        }
        
    except Exception as e:
        logger.error(f"更新基础数量失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"更新基础数量失败: {str(e)}")

def extract_numbers_from_specs(specs: str, base_unit: str = None) -> Optional[float]:
    """
    从商品规格中提取数量
    规则：
    1. 如果 base_unit 是标准单位（g、kg、斤、ml、L等），使用单位前的数字作为数量
    2. 否则，如果包含*，使用*后的数字作为数量
    3. 如果都不满足，返回1
    """
    if not specs:
        return None
        
    # 标准单位列表
    standard_units = ['g', 'kg', '斤', 'ml', 'L', '升', '克', '千克', '公斤']
    
    # 1. 首先检查 base_unit 是否是标准单位
    if base_unit and base_unit.lower() in [u.lower() for u in standard_units]:
        # 使用正则表达式匹配单位前的数字
        pattern = rf'(\d+(?:\.\d+)?)\s*{base_unit}'
        match = re.search(pattern, specs, re.IGNORECASE)
        if match:
            try:
                return float(match.group(1))
            except ValueError:
                pass
    
    # 2. 如果不包含标准单位，检查是否包含*
    if '*' in specs:
        # 匹配*后的数字
        pattern = r'\*(\d+(?:\.\d+)?)'
        match = re.search(pattern, specs)
        if match:
            try:
                return float(match.group(1))
            except ValueError:
                pass
    
    # 3. 如果都不满足，返回1
    return 1.0

def save_task_progress(task_id: str, progress_data: dict):
    """保存任务进度到数据库或缓存"""
    # TODO: 实现进度保存逻辑
    pass 

@router.post('/clean-sku-data')
async def clean_sku_data(db: Session = Depends(get_db)):
    """
    清洗SKU数据接口
    1. 获取所有SKU信息并遍历
    2. 检查is_handled和human_tag字段，任一为true则跳过
    3. 记录item_name字段
    4. 去除营销词
    5. 提取规格信息
    6. 识别品牌
    7. 提取单位
    8. 计算数量
    9. 返回所有SKU信息
    """
    try:
        # 获取所有SKU信息
        skus = db.query(models.ItemSKU).all()
        
        # 用于存储处理结果
        processed_skus = []
        
        # 获取营销词列表
        marketing_words = db.query(MarketingWordConfig.word).all()
        marketing_words = [word[0] for word in marketing_words]
        
        # 获取品牌列表
        brands = db.query(BrandConfig.name).all()
        brands = [brand[0] for brand in brands]
        
        # 规格匹配正则表达式列表
        spec_patterns = [
            # 这里添加规格匹配的正则表达式
            r'(?:\d+\.?\d*\s*[a-zA-Z]+\s*\*\d+\s*[罐瓶袋包份个枚]\s*\+?)+',
            r'(?:约|净含量)?\s*(\d+\.?\d*)\s*(?:[mM][lL]|[gG]|[kK][gG]|[lL]|[枚袋盒罐瓶包条只份个])\s*(?:装|\*\d+\s*[罐瓶袋包份个枚]|/(?:只|条|袋|包|盒|罐|瓶|枚))?\b'
        ]
        
        # 标准单位列表（按长度从长到短排序）
        standard_units = ['千克', '公斤', '毫升', '升', '克', '斤', 'ml', 'kg', 'g', 'l']
        
        # 包装单位规则库
        package_unit_rules = {
            "油类": {
                "unit": "瓶",
                "specs": ["ml", "g", "l","kg","ML","L","KG","G"],
                "keywords": ["香油", "芝麻油", "花生油", "菜籽油", "橄榄油", "调和油"]
            },
            "调味料": {
                "unit": "包",
                "specs": ["ml", "g", "l","kg","ML","L","KG","G"],
                "keywords": ["调味料", "调味包", "调料", "调味品"]
            },
            "火锅底料": {
                "unit": "袋",
                "specs": ["ml", "g", "l","kg","ML","L","KG","G"],
                "keywords": ["火锅底料", "火锅料", "底料","火锅底料包","火锅料包"]
            },
            "酱料": {
                "unit": "瓶",
                "specs": ["ml", "g", "l","kg","ML","L","KG","G"],
                "keywords": ["酱油", "醋", "料酒", "蚝油", "豆瓣酱", "辣椒酱","米醋"]
            },
            "饮料": {
                "unit": "瓶",
                "specs": ["ml", "g", "l","kg","ML","L","KG","G"],
                "keywords": ["饮料", "茶", "果汁", "碳酸饮料","混合汁","果味饮料","果味水","果味茶","果味饮料水","果味茶水","咖啡","汽水","椰子水","饮品"]
            },
            "酒类": {
                "unit": "瓶",
                "specs": ["ml", "g", "l","kg","ML","L","KG","G"],
                "keywords": ["白酒", "红酒", "啤酒", "葡萄酒", "洋酒", "黄酒", "果酒", "鸡尾酒", "威士忌", "白兰地", "伏特加", "龙舌兰", "朗姆酒", "利口酒", "甜酒", "果酒", "果味酒","贵宾酒","泸州老窖","礼宾酒","纪念酒","特曲酒","经典酒","果露酒","二锅头","舍得酒"]
            },
            "桶面": {
                "unit": "桶",
                "specs": ["ml", "g", "l","kg","ML","L","KG","G"],
                "keywords": ["桶面","杯面"]
            },
            "礼盒": {
                "unit": "盒",
                "specs": ["ml", "g", "l","kg","ML","L","KG","G"],
                "keywords": ["礼盒","盒装"]
            }
        }
        
        # 遍历处理每个SKU
        for sku in skus:
            try:
                # 检查是否需要跳过处理
                if sku.is_handled == 1 or sku.human_tag == 1:
                    processed_skus.append({
                        "item_name": sku.item_name,
                        "item_brand": sku.item_brand,
                        "item_specs": sku.item_specs,
                        "item_price": sku.item_price,
                        "item_classification": sku.item_classification,
                        "base_price": sku.base_price,
                        "base_unit": sku.base_unit,
                        "base_num": sku.base_num,
                        "sku": sku.sku,
                        "cleaned_name": sku.cleaned_name,
                        "package_type": sku.package_type
                    })
                    continue
                
                # 记录原始item_name
                original_name = sku.item_name
                current_name = original_name
                specs = None
                brand = None
                unit = None
                quantity = 1.0
                
                # 4. 去除营销词
                for word in marketing_words:
                    if word in current_name:
                        current_name = current_name.replace(word, '')
                        logger.info(f"SKU {sku.id} 去除营销词: {word}")
                
                # 5. 提取规格信息
                for pattern in spec_patterns:
                    match = re.search(pattern, current_name)
                    if match:
                        specs = match.group(0)
                        current_name = current_name.replace(specs, '')
                        logger.info(f"SKU {sku.id} 提取规格信息: {specs}")
                        break
                
                # 6. 识别品牌
                for brand_name in brands:
                    if brand_name in current_name:
                        brand = brand_name
                        current_name = current_name.replace(brand_name, '')
                        logger.info(f"SKU {sku.id} 识别品牌: {brand}")
                        break
                
                # 7. 提取单位
                if specs:
                    # 首先检查是否有"数值+单位/包装单位"的格式
                    package_unit_pattern = r'(\d+(?:\.\d+)?)\s*(?:[mM][lL]|[gG]|[kK][gG]|[lL])\s*/\s*([袋包盒罐瓶枚只条个份])'
                    package_match = re.search(package_unit_pattern, specs)
                    if package_match:
                        unit = package_match.group(2)  # 直接使用包装单位
                        logger.info(f"SKU {sku.id} 从规格中提取包装单位: {unit}")
                    else:
                        # 如果没有找到包装单位格式，则使用原来的逻辑
                        for unit_name in standard_units:
                            if unit_name in specs:
                                unit = unit_name
                                logger.info(f"SKU {sku.id} 提取单位: {unit}")
                                break
                
                # 8. 确定包装单位
                if unit and not package_match:  # 只有在没有找到包装单位格式时才进行规则匹配
                    # 遍历规则库查找匹配的包装单位
                    for product_type, rule in package_unit_rules.items():
                        # 检查商品名称是否包含关键词
                        if any(keyword in original_name.lower() for keyword in rule["keywords"]):
                            # 检查规格单位是否在规则允许的范围内
                            if unit.lower() in rule["specs"]:
                                unit = rule["unit"]  # 直接替换单位
                                logger.info(f"SKU {sku.id} 确定包装单位: {unit}")
                                break
                
                # 9. 计算数量
                if specs:
                    # 标准单位列表
                    standard_units = ['ml', 'g', 'kg', 'l', 'ML', 'G', 'KG', 'L']
                    
                    if unit:
                        # 检查是否是标准单位
                        if unit.lower() in [u.lower() for u in standard_units]:
                            # 如果是标准单位，使用单位前的数字
                            pattern = rf'(\d+(?:\.\d+)?)\s*{unit}'
                            match = re.search(pattern, specs)
                            if match:
                                quantity = float(match.group(1))
                                logger.info(f"SKU {sku.id} 从标准单位前提取数量: {quantity}")
                        else:
                            # 如果是包装单位（瓶、袋等），检查是否有*号
                            if '*' in specs:
                                match = re.search(r'\*(\d+(?:\.\d+)?)', specs)
                                if match:
                                    quantity = float(match.group(1))
                                    logger.info(f"SKU {sku.id} 从*后提取包装数量: {quantity}")
                                else:
                                    quantity = 1.0  # 如果没有找到*号后的数字，默认为1
                                    logger.info(f"SKU {sku.id} 未找到包装数量，默认为1")
                            else:
                                quantity = 1.0  # 如果没有*号，默认为1
                                logger.info(f"SKU {sku.id} 未找到包装数量，默认为1")
                    else:
                        # 检查是否存在*或+
                        if '*' in specs:
                            match = re.search(r'\*(\d+(?:\.\d+)?)', specs)
                            if match:
                                quantity = float(match.group(1))
                                logger.info(f"SKU {sku.id} 从*后提取数量: {quantity}")
                            elif '+' in specs:
                                numbers = re.findall(r'(\d+(?:\.\d+)?)', specs)
                                if len(numbers) >= 2:
                                    quantity = sum(float(num) for num in numbers)
                                    logger.info(f"SKU {sku.id} 从+前后提取数量: {quantity}")
                        elif '+' in specs:
                            numbers = re.findall(r'(\d+(?:\.\d+)?)', specs)
                            if len(numbers) >= 2:
                                quantity = sum(float(num) for num in numbers)
                                logger.info(f"SKU {sku.id} 从+前后提取数量: {quantity}")
                        else:
                            quantity = 1.0  # 如果没有找到任何数量信息，默认为1
                            logger.info(f"SKU {sku.id} 未找到数量信息，默认为1")
                
                # 10. 保存处理结果到对应字段
                sku.cleaned_name = current_name.strip()
                sku.item_brand = brand
                sku.item_specs = specs
                sku.base_num = quantity
                sku.base_unit = unit  # 直接使用包装单位
                sku.is_handled = True  # 设置is_handled为true
                
                # 将处理后的SKU信息添加到结果列表
                processed_skus.append({
                    "item_name": original_name,
                    "item_brand": sku.item_brand,
                    "item_specs": sku.item_specs,
                    "item_price": sku.item_price,
                    "item_classification": sku.item_classification,
                    "base_price": sku.base_price,
                    "base_unit": sku.base_unit,
                    "base_num": sku.base_num,
                    "sku": sku.sku,
                    "cleaned_name": sku.cleaned_name,
                    "package_type": sku.package_type,
                    "is_handled": sku.is_handled
                })
                
            except Exception as e:
                logger.error(f"处理SKU {sku.id} 时出错: {str(e)}")
                continue
        
        # 提交所有更改到数据库
        try:
            db.commit()
            logger.info("成功保存所有SKU更新")
        except Exception as e:
            db.rollback()
            logger.error(f"保存SKU更新失败: {str(e)}")
            raise HTTPException(status_code=500, detail=f"保存SKU更新失败: {str(e)}")
        
        # 返回所有SKU信息
        return {
            "success": True,
            "total_count": len(skus),
            "processed_count": len(processed_skus),
            "data": processed_skus
        }
        
    except Exception as e:
        logger.error(f"清洗SKU数据失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"清洗SKU数据失败: {str(e)}")

